package com.th.dao;

import com.th.contraller.UserMag;
import com.th.entity.Obj;
import org.slf4j.Logger;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
@Component
public class ObjDaoImp implements ObjDao{
    JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void applyUpObj(Obj obj) {
        String sql = "insert into get_obj (get_id,get_date,get_add,obj_type,get_name,get_detail,get_num,get_states,img)" +
                "values (?,?,?,?,?,?,?,?,?)";
        Integer result = jdbcTemplate.update(sql,obj.getGet_id(),obj.getGet_date(),obj.getGet_add(),obj.getObj_type(),obj.getGet_name(),obj.getGet_detail(),obj.getGet_num(),obj.getGet_states(),obj.getImg());
    }

    @Override
    public void deleteObj(int objId) {
        String sql = "delete from get_obj where get_id=?";
        jdbcTemplate.update(sql,objId);
    }

    @Override
    @Scheduled(cron = "0 0 0 * * ?")
    public void UpObjTimeOut() {
        String sql = "delete from get_obj where get_states=0";
        jdbcTemplate.update(sql);
    }

    @Override
    public void applyGetObj(int get_id,String openid) {
        Date date =new Date();
        String sql = "update get_obj set get_states=2,lost_date=?, lost_num=? where get_id=?";
        jdbcTemplate.update(sql,date,openid,get_id);
    }

    @Override
    @Scheduled(cron = "0 0 0 * * ?")
    public void GetObjTimeOut() {
        String sql = "update get_obj set get_states=1, lost_num=NULL, lost_date = NULL where get_states=2";
        jdbcTemplate.update(sql);
    }

    @Override
    public void agreeGetObj(int objId) {
        String sql = "update get_obj set get_states=3 where get_id=?";
        jdbcTemplate.update(sql,objId);
    }

    @Override
    public void agreeUpObj(int objId) {
        String sql = "update get_obj set get_states=1 where get_id=?";
        jdbcTemplate.update(sql,objId);
    }

    @Override
    public List<Obj> selectObjByGetNum(String openid) {
        String sql = "select * from get_obj where get_num=?";
        List<Obj> objs = jdbcTemplate.query(sql,new Object[]{openid},new RowMapper<Obj>() {

            @Override
            public Obj mapRow(ResultSet rs, int rowNum) throws SQLException {
                Obj obj = new Obj();
                obj.setGet_id(rs.getInt("get_id"));
                obj.setGet_states(rs.getInt("get_states"));
                obj.setGet_date(rs.getDate("get_date"));
                obj.setObj_type(rs.getString("obj_type"));
                obj.setGet_name(rs.getString("get_name"));
                obj.setGet_detail(rs.getString("get_detail"));
                obj.setGet_add(rs.getString("get_add"));
                obj.setGet_num(rs.getString("get_num"));
                obj.setLost_num(rs.getString("lost_num"));
                if(rs.getDate("lost_date")!=null)
                {
                    obj.setLost_date(rs.getDate("lost_date").toString());
                }
                obj.setImg(rs.getString("img"));
                return obj;
            }
        });
        return objs;
    }

    @Override
    public List<Obj> selectObjsByType(int page, int pageSize, String type) {
        String sql = "select * from get_obj where obj_type=\'"+type+"\' and get_states = 1 limit "+pageSize+" OFFSET "+(page-1)*pageSize;
        List<Obj> objs = jdbcTemplate.query(sql,new RowMapper<Obj>() {

            @Override
            public Obj mapRow(ResultSet rs, int rowNum) throws SQLException {
                Obj obj = new Obj();
                obj.setGet_id(rs.getInt("get_id"));
                obj.setGet_states(rs.getInt("get_states"));
                obj.setGet_date(rs.getDate("get_date"));
                obj.setObj_type(rs.getString("obj_type"));
                obj.setGet_name(rs.getString("get_name"));
                obj.setGet_detail(rs.getString("get_detail"));
                obj.setGet_add(rs.getString("get_add"));
                obj.setGet_num(rs.getString("get_num"));
                obj.setLost_num(rs.getString("lost_num"));
                if(rs.getDate("lost_date")!=null)
                {
                    obj.setLost_date(rs.getDate("lost_date").toString());
                }
                obj.setImg(rs.getString("img"));
                return obj;
            }
        });
        return objs;
    }

    @Override
    public List<Obj> selectObjs(int page, int pageSize, int states) {
        String sql = "select * from get_obj where get_states="+states+" limit "+pageSize+" OFFSET "+(page-1)*pageSize;
        List<Obj> objs = jdbcTemplate.query(sql,new RowMapper<Obj>() {

            @Override
            public Obj mapRow(ResultSet rs, int rowNum) throws SQLException {
                Obj obj = new Obj();
                obj.setGet_id(rs.getInt("get_id"));
                obj.setGet_states(rs.getInt("get_states"));
                obj.setGet_date(rs.getDate("get_date"));
                obj.setObj_type(rs.getString("obj_type"));
                obj.setGet_name(rs.getString("get_name"));
                obj.setGet_detail(rs.getString("get_detail"));
                obj.setGet_add(rs.getString("get_add"));
                obj.setGet_num(rs.getString("get_num"));
                obj.setLost_num(rs.getString("lost_num"));
                if(rs.getDate("lost_date")!=null)
                {
                    obj.setLost_date(rs.getDate("lost_date").toString());
                }
                obj.setImg(rs.getString("img"));
                return obj;
            }
        });
        return objs;
    }

    @Override
    public List<Obj> selectObjsByKeys(int page, int pageSize, String keys) {
        StringBuffer buffer = new StringBuffer();
        buffer.append("%");
        int n = keys.length();
        for(int i = 0; i < n; i++)
        {
            buffer.append(keys.charAt(i)+"%");
        }
        String regex = buffer.toString();
        String sql = "select * from get_obj where  (get_name like \'"+regex+"\' or get_detail like \'"+regex+"\' or get_add like \'"+regex+"\' or get_date like \'"+regex+"\') and get_states = 1  limit "+pageSize+" OFFSET " +(page-1)*pageSize;
        return jdbcTemplate.query(sql,new RowMapper<Obj>() {
            @Override
            public Obj mapRow(ResultSet rs, int rowNum) throws SQLException {
                Obj obj = new Obj();
                obj.setGet_id(rs.getInt("get_id"));
                obj.setGet_states(rs.getInt("get_states"));
                obj.setGet_date(rs.getDate("get_date"));
                obj.setObj_type(rs.getString("obj_type"));
                obj.setGet_name(rs.getString("get_name"));
                obj.setGet_detail(rs.getString("get_detail"));
                obj.setGet_add(rs.getString("get_add"));
                obj.setGet_num(rs.getString("get_num"));
                obj.setLost_num(rs.getString("lost_num"));
                if(rs.getDate("lost_date")!=null)
                {
                    obj.setLost_date(rs.getDate("lost_date").toString());
                }
                obj.setImg(rs.getString("img"));
                return obj;
            }
        });
    }

    @Override
    public Obj selectObjById(int get_id) {
        String sql = "select * from get_obj where get_id="+get_id;
        return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Obj>(Obj.class));
    }


    @Override
    public List<Obj> selectMyUps(String openid) {
        String sql = "select * from get_obj where get_num=\'"+openid+"\' and (get_states = 0 OR get_states = 1)";
        return jdbcTemplate.query(sql,new RowMapper<Obj>() {
            @Override
            public Obj mapRow(ResultSet rs, int rowNum) throws SQLException {
                Obj obj = new Obj();
                obj.setGet_id(rs.getInt("get_id"));
                obj.setGet_states(rs.getInt("get_states"));
                obj.setGet_date(rs.getDate("get_date"));
                obj.setObj_type(rs.getString("obj_type"));
                obj.setGet_name(rs.getString("get_name"));
                obj.setGet_detail(rs.getString("get_detail"));
                obj.setGet_add(rs.getString("get_add"));
                obj.setGet_num(rs.getString("get_num"));
                obj.setLost_num(rs.getString("lost_num"));
                if(rs.getDate("lost_date")!=null)
                {
                    obj.setLost_date(rs.getDate("lost_date").toString());
                }
                obj.setImg(rs.getString("img"));
                return obj;
            }
        });
    }

    @Override
    public List<Obj> selectMyGets(String openid) {
        String sql = "select * from get_obj where lost_num=\'"+openid+"\' and get_states = 2";
        return jdbcTemplate.query(sql,new RowMapper<Obj>() {
            @Override
            public Obj mapRow(ResultSet rs, int rowNum) throws SQLException {
                Obj obj = new Obj();
                obj.setGet_id(rs.getInt("get_id"));
                obj.setGet_states(rs.getInt("get_states"));
                obj.setGet_date(rs.getDate("get_date"));
                obj.setObj_type(rs.getString("obj_type"));
                obj.setGet_name(rs.getString("get_name"));
                obj.setGet_detail(rs.getString("get_detail"));
                obj.setGet_add(rs.getString("get_add"));
                obj.setGet_num(rs.getString("get_num"));
                obj.setLost_num(rs.getString("lost_num"));
                if(rs.getDate("lost_date")!=null)
                {
                    obj.setLost_date(rs.getDate("lost_date").toString());
                }
                obj.setImg(rs.getString("img"));
                return obj;
            }
        });
    }

    @Override
    public void updateObj(Obj obj) {
        String sql = "update get_obj set img=? where get_id=?";
        jdbcTemplate.update(sql,obj.getImg(),obj.getGet_id());
    }

}
